package com.dao.Impl;

import com.dao.UserDao;
import com.entity.PageBean;
import com.entity.User;
import com.util.JDBC;
import com.util.TransBean;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserDaoImpl implements UserDao {
    @Override
    public int delete(int id) {
        return 0;
    }

    @Override
    public List<User> findAll(int a, int b) {
        List<List> lists = JDBC.selectArray("select id,userId,userName,phone,status,idCard,birthday,sex,address,area,createdTime,cardId,momo,staffId from user limit 0,5");
        if (lists != null) {
            List<User> ulist = new ArrayList<>();
            for (List list : lists) {
                ulist.add(new User(
                        (Integer) list.get(0),
                        (Integer) list.get(1),
                        (String) list.get(2),
                        (String) list.get(3),
                        (Integer) list.get(4),
                        (String) list.get(5),
                        (String) list.get(6),
                        (Integer) list.get(7),
                        (String) list.get(8),
                        (String) list.get(9),
                        Timestamp.valueOf(String.valueOf(list.get(10))),
                        (Integer) list.get(11),
                        (String) list.get(12),
                        (Integer) list.get(13)
                ));
            }
            return ulist;
        }
        return null;
    }


    /**
     * 查询会员列表信息
     * @return
     */
    @Override
    public List<User> findAllMember() {
        List<List> lists = JDBC.selectArray("select id,userId,userName,phone,idCard,address,createdTime,cardId from user limit 0,10");
        if (lists!= null) {
            List<User> ulist = new ArrayList<>();
            for (List list : lists) {
                ulist.add(new User(
                        (Integer) list.get(0),
                        (Integer) list.get(1),
                        (String) list.get(2),
                        (String) list.get(3),
                        (String) list.get(4),
                        (String) list.get(5),
                        Timestamp.valueOf(String.valueOf(list.get(6))),
                        (Integer) list.get(7)
                ));
            }
            return ulist;
        }
        return null;
    }


    /**
     * 根据会员编号查询
     * @param userId
     * @return
     */
    @Override
    public List<User> findOne(int userId) {
        List<List> lists = JDBC.selectArray("select id,userId,userName,phone,status,idCard,address,createdTime,cardId from user where userId=?",userId);
        if (lists!= null) {
            List<User> ulist = new ArrayList<>();
            for (List list : lists) {
                ulist.add(new User(
                        (Integer) list.get(0),
                        (Integer) list.get(1),
                        (String) list.get(2),
                        (String) list.get(3),
                        (Integer) list.get(4),
                        (String) list.get(5),
                        (String) list.get(6),
                        Timestamp.valueOf(String.valueOf(list.get(7))),
                        (Integer) list.get(8)
                ));
                return ulist;
            }
        }
        return null;
    }

    @Override
    public User finduserId(int userId) {
        List<Map> maps = JDBC.selectMap("select id,userId,userName,phone,status,idCard,address,createdTime,cardId from user where userId=?",userId);
        List<User> list = TransBean.populate(User.class, maps);
        if(list!=null){
            return list.get(0);
        }
       return null;
    }

    /**
     * 根据名字模糊查询
     * @param name
     * @return
     */
    @Override
    public List<User> findName(String name) {
        return null;
    }


    /**
     *分页查询
     * @param page
     */
    @Override
    public void findPageBean(PageBean<User> page) {
        String sql="select count(*) from user where 1=1 ";
        String condstr="";
        if (page.getFactor()!=null){
            for (String s : page.getFactor()) {
                condstr+=" and "+s;
            }
        }
        page.setStartIndex();
        List<List> lists=JDBC.selectArray((sql+(condstr==null?"":condstr)));
        page.setRowCount(((Long)lists.get(0).get(0)).intValue());
        page.getTotalPage();
        List<User> list=new ArrayList<>();
        sql=String.format("select id,userId,userName,phone,status,idCard,address,createdTime,cardId from user where 1=1 %s limit ?,? ",(condstr==null?"":condstr));
        List<List> listList=JDBC.selectArray(sql,(page.getPageNo() - 1) * page.getPageSize(), page.getPageSize());
        if (listList.size()>0){
            for (List list1 : listList) {
                list.add(new User(
                        (Integer) list1.get(0),
                        (Integer) list1.get(1),
                        (String) list1.get(2),
                        (String)list1.get(3),
                        (Integer) list1.get(4),
                        (String)list1.get(5),
                        (String)list1.get(6),
                        (Timestamp) list1.get(7),
                        (Integer) list1.get(8)
                ));
            }
        }
        page.setData(list);

    }


    /**
     *会员信息分页查询
     * @param page
     */
    public void findMemberDetails(PageBean<User> page) {
        String sql="select count(*) from user where 1=1 ";
        String condstr="";
        if (page.getFactor()!=null){
            for (String s : page.getFactor()) {
                condstr+=" and "+s;
            }
        }
        page.setStartIndex();
        List<List> lists=JDBC.selectArray((sql+(condstr==null?"":condstr)));
        page.setRowCount(((Long)lists.get(0).get(0)).intValue());
        page.getTotalPage();
        sql="select user.cardId,userName,user.userId,card.levelId,amount,credit,card.status from user,card where user.cardId=card.cardId  limit ?,?";
        List<Map> listList=JDBC.selectMap(sql,(page.getPageNo() - 1) * page.getPageSize(), page.getPageSize());
        if (listList.size()>0){
            page.setData1(listList);
        }

    }

    @Override
    public int deleteId(int id) {
        JDBC.sqlUpdate("delete from user where id=?",id);
        return -1;
    }

    @Override
    public int alter(User user) {
        return JDBC.sqlUpdate("update user set userName=?,phone=?,status=?,idCard=?,address=? where userId=?",
                user.getUserName(),user.getPhone(),user.getStatus(),user.getIdCard(),user.getAddress(),user.getUserId());
    }

    //  查询每个月的人数的数量，做统计图用
    @Override
    public Map findsum() {
        // select MONTH(`user`.createdTime),id from `user` GROUP BY MONTH(`user`.createdTime) asc
        List<List> lists = JDBC.selectArray("select MONTH(user.createdTime),count(*) from user GROUP BY MONTH(user.createdTime) ");
        Map map=new HashMap();
        for (List o : lists) {
           map.put(o.get(0),o.get(1));
        }
        return map;
    }

    /**
     * 查询user表中的userId最后一个
     * @return
     */
    @Override
    public int findUserId() {
        List<List> lists = JDBC.selectArray("select userId from user order by id desc limit 1");
        return (Integer)lists.get(0).get(0);
    }

    /**
     * 会员登记添加用户信息
     * @param user
     */
    @Override
    public void insertUser(User user) {
        JDBC.sqlUpdate("insert into user(id,userId,userName,phone,status,idCard,birthday,sex,address,area,createdTime,cardId,momo,staffId) values(null,?,?,?,?,?,?,?,?,?,now(),?,?,?)",
                user.getUserId(),
                user.getUserName(),
                user.getPhone(),
                user.getStatus(),
                user.getIdCard(),
                user.getBirthday(),
                user.getSex(),
                user.getAddress(),
                user.getArea(),
                user.getCardId(),
                user.getMomo(),
                user.getStaffId()
                );
    }
}
